const express = require('express');
const sql = require('mssql');
const cors = require('cors');
const bodyParser = require('body-parser');
const path = require('path');
const ExcelJS = require('exceljs');

const app = express();
const PORT = process.env.PORT || 3000;

// 中间件
app.use(cors());
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));

// 存储数据库连接配置
let dbConfig = null;
let pool = null;

// 测试数据库连接
app.post('/api/test-connection', async (req, res) => {
    try {
        const { server, database, user, password, port = 1433 } = req.body;
        
        const config = {
            server,
            database,
            user,
            password,
            port: parseInt(port),
            options: {
                encrypt: false,
                trustServerCertificate: true
            }
        };

        // 创建新的连接池进行测试
        const testPool = new sql.ConnectionPool(config);
        await testPool.connect();
        await testPool.close();

        // 测试成功，保存配置
        dbConfig = config;
        
        res.json({ success: true, message: '数据库连接测试成功' });
    } catch (error) {
        console.error('数据库连接测试失败:', error);
        res.status(500).json({ success: false, message: '数据库连接失败: ' + error.message });
    }
});

// 获取数据库中所有表
app.get('/api/tables', async (req, res) => {
    try {
        if (!dbConfig) {
            return res.status(400).json({ success: false, message: '请先配置数据库连接' });
        }

        if (!pool) {
            pool = new sql.ConnectionPool(dbConfig);
            await pool.connect();
        }

        const result = await pool.request().query(`
            SELECT 
                TABLE_NAME,
                TABLE_SCHEMA
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE = 'BASE TABLE'
            ORDER BY TABLE_SCHEMA, TABLE_NAME
        `);

        res.json({ success: true, tables: result.recordset });
    } catch (error) {
        console.error('获取表列表失败:', error);
        res.status(500).json({ success: false, message: '获取表列表失败: ' + error.message });
    }
});

// 获取表结构信息
app.get('/api/table/:tableName/structure', async (req, res) => {
    try {
        if (!dbConfig) {
            return res.status(400).json({ success: false, message: '请先配置数据库连接' });
        }

        if (!pool) {
            pool = new sql.ConnectionPool(dbConfig);
            await pool.connect();
        }

        const tableName = req.params.tableName;
        
        const result = await pool.request()
            .input('tableName', sql.NVarChar, tableName)
            .query(`
                SELECT 
                    COLUMN_NAME,
                    DATA_TYPE,
                    IS_NULLABLE,
                    CHARACTER_MAXIMUM_LENGTH,
                    NUMERIC_PRECISION,
                    NUMERIC_SCALE,
                    COLUMN_DEFAULT
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = @tableName
                ORDER BY ORDINAL_POSITION
            `);

        res.json({ success: true, structure: result.recordset });
    } catch (error) {
        console.error('获取表结构失败:', error);
        res.status(500).json({ success: false, message: '获取表结构失败: ' + error.message });
    }
});

// 获取表数据（支持分页）
app.get('/api/table-data/:tableName', async (req, res) => {
    try {
        if (!dbConfig) {
            return res.status(400).json({ success: false, error: '请先配置数据库连接' });
        }

        if (!pool) {
            pool = new sql.ConnectionPool(dbConfig);
            await pool.connect();
        }

        const tableName = req.params.tableName;
        const page = parseInt(req.query.page) || 1;
        const pageSize = parseInt(req.query.pageSize) || 50;
        const offset = (page - 1) * pageSize;

        // 获取总记录数
        const countResult = await pool.request()
            .input('tableName', sql.NVarChar, tableName)
            .query(`SELECT COUNT(*) as total FROM [${tableName}]`);
        
        const total = countResult.recordset[0].total;

        // 获取分页数据
        const dataResult = await pool.request()
            .input('tableName', sql.NVarChar, tableName)
            .input('offset', sql.Int, offset)
            .input('pageSize', sql.Int, pageSize)
            .query(`
                SELECT * FROM [${tableName}]
                ORDER BY (SELECT NULL)
                OFFSET @offset ROWS
                FETCH NEXT @pageSize ROWS ONLY
            `);

        // 获取列信息
        let columns = [];
        if (dataResult.recordset && dataResult.recordset.length > 0) {
            columns = Object.keys(dataResult.recordset[0]);
        } else {
            // 如果没有数据，从表结构获取列信息
            const structureResult = await pool.request()
                .input('tableName', sql.NVarChar, tableName)
                .query(`
                    SELECT COLUMN_NAME
                    FROM INFORMATION_SCHEMA.COLUMNS 
                    WHERE TABLE_NAME = @tableName
                    ORDER BY ORDINAL_POSITION
                `);
            columns = structureResult.recordset.map(row => row.COLUMN_NAME);
        }

        res.json({ 
            success: true, 
            data: dataResult.recordset || [],
            columns: columns,
            total: total,
            page: page,
            pageSize: pageSize,
            totalPages: Math.ceil(total / pageSize)
        });
    } catch (error) {
        console.error('获取表数据失败:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// 导出表数据为Excel
app.get('/api/table/:tableName/export', async (req, res) => {
    try {
        if (!dbConfig) {
            return res.status(400).json({ success: false, message: '请先配置数据库连接' });
        }

        if (!pool) {
            pool = new sql.ConnectionPool(dbConfig);
            await pool.connect();
        }

        const tableName = req.params.tableName;

        // 获取表结构
        const structureResult = await pool.request()
            .input('tableName', sql.NVarChar, tableName)
            .query(`
                SELECT COLUMN_NAME
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_NAME = @tableName
                ORDER BY ORDINAL_POSITION
            `);

        // 获取所有数据
        const dataResult = await pool.request()
            .query(`SELECT * FROM [${tableName}]`);

        // 创建Excel工作簿
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet(tableName);

        // 添加表头
        const columns = structureResult.recordset.map(col => ({
            header: col.COLUMN_NAME,
            key: col.COLUMN_NAME,
            width: 15
        }));
        worksheet.columns = columns;

        // 添加数据
        dataResult.recordset.forEach(row => {
            worksheet.addRow(row);
        });

        // 设置响应头
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', `attachment; filename="${tableName}.xlsx"`);

        // 写入响应
        await workbook.xlsx.write(res);
        res.end();

    } catch (error) {
        console.error('导出Excel失败:', error);
        res.status(500).json({ success: false, message: '导出Excel失败: ' + error.message });
    }
});

// 执行自定义SQL查询
// 执行SQL查询
app.post('/api/execute-query', async (req, res) => {
    try {
        if (!dbConfig) {
            return res.status(400).json({ success: false, error: '请先配置数据库连接' });
        }

        if (!pool) {
            pool = new sql.ConnectionPool(dbConfig);
            await pool.connect();
        }

        const { query } = req.body;
        
        if (!query || !query.trim()) {
            return res.status(400).json({ success: false, error: '查询语句不能为空' });
        }

        // 增强的SQL注入防护和语法检查
        const trimmedQuery = query.trim().toLowerCase();
        
        // 检查是否为SELECT语句
        if (!trimmedQuery.startsWith('select')) {
            return res.status(400).json({ success: false, error: '仅支持SELECT查询语句' });
        }

        // 检查危险关键词
        const dangerousKeywords = ['drop', 'delete', 'update', 'insert', 'alter', 'create', 'truncate', 'exec', 'execute', 'sp_', 'xp_'];
        const hasDangerousKeyword = dangerousKeywords.some(keyword => 
            trimmedQuery.includes(keyword.toLowerCase())
        );
        
        if (hasDangerousKeyword) {
            return res.status(400).json({ success: false, error: '查询语句包含不允许的关键词' });
        }

        // 检查基本语法错误
        const openParens = (query.match(/\(/g) || []).length;
        const closeParens = (query.match(/\)/g) || []).length;
        if (openParens !== closeParens) {
            return res.status(400).json({ success: false, error: '查询语句括号不匹配' });
        }

        const result = await pool.request().query(query);
        
        // 获取列信息
        let columns = [];
        if (result.recordset && result.recordset.length > 0) {
            columns = Object.keys(result.recordset[0]);
        } else if (result.recordset.columns) {
            columns = Object.keys(result.recordset.columns);
        }
        
        res.json({ 
            success: true, 
            data: result.recordset || [],
            columns: columns,
            rowsAffected: result.rowsAffected[0] || 0,
            message: `查询成功，返回 ${result.recordset ? result.recordset.length : 0} 条记录`
        });
    } catch (error) {
        console.error('执行查询失败:', error);
        
        // 提供更友好的错误信息
        let errorMessage = error.message;
        if (error.message.includes('Invalid object name')) {
            errorMessage = '表名或字段名不存在，请检查查询语句';
        } else if (error.message.includes('Incorrect syntax')) {
            errorMessage = 'SQL语法错误，请检查查询语句';
        } else if (error.message.includes('Invalid column name')) {
            errorMessage = '列名不存在，请检查字段名称';
        }
        
        res.status(500).json({ success: false, error: errorMessage });
    }
});

// 根路由
app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

// 启动服务器
app.listen(PORT, () => {
    console.log(`服务器运行在 http://localhost:${PORT}`);
});

// 优雅关闭
process.on('SIGINT', async () => {
    console.log('正在关闭服务器...');
    if (pool) {
        await pool.close();
    }
    process.exit(0);
});